﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Framework;

namespace Enterprise
{
    public class ColumnService : BaseService
    {

        /// <summary>
        /// 获取栏目列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable()
        {
            var table = new Table
            {
                Rows = DbHelperSql.Query("select * from TbColumn").Tables["ds"].Rows,
                Total = DbHelperSql.GetSingle("select count(*) from TbColumn").SafeToInt()
            };
            return table;
        }

        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            return DbHelperSql.Query("select A.CoName,A.CoNameEn,A.CoColumnId,A.CoSort,B.CoName as CoParentName  from TbColumn as A left join TbColumn as B on A.CoParentId=B.CoColumnId where A.CoColumnId={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }


        /// <summary>
        /// 添加栏目栏目
        /// </summary>
        /// <param name="name">栏目名称</param>
        /// <param name="nameEn">栏目名称(EN)</param>
        /// <param name="parentId">父级ID</param>
        /// <param name="sort">排序</param>
        public int Add(string name, string nameEn, int parentId, int sort)
        {

            Check.Argument.IsNotOutOfLength(name, 2, 50, "栏目名称");
            Check.Argument.IsNotOutOfLength(nameEn, 2, 50, "栏目名称(En)");
            var strSql = new StringBuilder();
            strSql.Append("insert into TbColumn(");
            strSql.Append("CoName,CoNameEn,CoColumnPath,CoParentId,CoDepth,CoType,CoLanguage,CoSort");
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append("@CoName,@CoNameEn,@CoColumnPath,@CoParentId,@CoDepth,@CoType,@CoLanguage,@CoSort");
            strSql.Append(")");
            SqlParameter[] parameters =
                {
                    new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoNameEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoColumnPath", SqlDbType.Text),
                    new SqlParameter("@CoParentId", SqlDbType.Int),
                    new SqlParameter("@CoDepth", SqlDbType.Int),
                    new SqlParameter("@CoType", SqlDbType.Int),
                    new SqlParameter("@CoLanguage", SqlDbType.VarChar, 10),
                    new SqlParameter("@CoSort", SqlDbType.Int)
                };
            parameters[0].Value = name;
            parameters[1].Value = nameEn;
            using (var sdr = DbHelperSql.ExecuteReader("select * from TbColumn where CoColumnId={0}".FormatWith(parentId)))
            {
                if (sdr.Read())
                {

                    parameters[2].Value = sdr["CoColumnPath"].SafeToString() + parentId + ",";
                    parameters[3].Value = parentId;
                    parameters[4].Value = sdr["CoDepth"].SafeToInt() + 1;

                }
                else
                {
                    parameters[2].Value = "";
                    parameters[3].Value = DBNull.Value;
                    parameters[4].Value = 1;
                }
            }
            parameters[5].Value = 0;
            parameters[6].Value = DBNull.Value;
            parameters[7].Value = sort;
            var obj = DbHelperSql.Add(strSql.ToString(), parameters).SafeToInt();
            return obj;

        }

        /// <summary>
        /// 编辑栏目栏目
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="nameEn"></param>
        /// <param name="sort"></param>
        public void Edit(int id, string name, string nameEn, int sort)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "栏目名称");
            Check.Argument.IsNotOutOfLength(nameEn, 2, 50, "栏目名称(En)");
            var sbSql = new StringBuilder();
            sbSql.Append("update TbColumn set ");
            sbSql.Append("CoName=@CoName, ");
            sbSql.Append("CoNameEn=@CoNameEn, ");
            sbSql.Append("CoSort=@CoSort ");
            sbSql.Append("where CoColumnId=@id ");
            SqlParameter[] parameters =
                {

                    new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoNameEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoSort", SqlDbType.Int),
                    new SqlParameter("@id", SqlDbType.Int)
                };
            parameters[0].Value = name;
            parameters[1].Value = nameEn;
            parameters[2].Value = sort;
            parameters[3].Value = id;
            var obj = DbHelperSql.ExecuteSql(sbSql.ToString(), parameters).SafeToInt();
            if (!(obj > 0))
            {
                throw new Exception("记录不存在！");
            }
        }

        /// <summary>
        /// 删除栏目栏目
        /// </summary>
        /// <param name="id"></param>
        public void Delete(int id)
        {
            var syscount = DbHelperSql.GetSingle("select count(*) from TbColumn where CoColumnId={0} and coissystem=1".FormatWith(id)).SafeToInt();
            if (syscount > 0)
            {
                throw new Exception("系统项不能删除！");
            }
            var childcount = DbHelperSql.GetSingle("select count(*) from TbColumn where coparentid={0}".FormatWith(id)).SafeToInt();
            if (childcount > 0)
            {
                throw new Exception("该栏目存在子类，请先删除子类！");
            }
            var count = DbHelperSql.GetSingle("select count(*) from TbPage where CoColumnId={0}".FormatWith(id)).SafeToInt();
            if (count > 0)
            {
                throw new Exception("该栏目下存在页面，请先删除页面记录！");
            }
            var obj = DbHelperSql.ExecuteSql("delete from TbColumn where CoColumnId={0}".FormatWith(id)).SafeToInt();
            if (!(obj > 0))
            {
                throw new Exception("记录不存在！");
            }
        }
    }
}